When using a database, you can use conditions (WHERE clause) and sorting (ORDER BY) to retrieve the desired data.
Since the format of WHERE and ORDER BY clauses differs for each database, users must refer to the SQL filter statements of the respective database.
Here, we will explain the format for the most commonly used MS Access file (mdb).
Note: When creating a database (mdb file), if you name the fields in each table with names such as DATE, NO, ORDER, etc., external programs cannot access them.
This is because Access DB recognizes these characters as its special characters. Users must understand and use this based on the type of DB program they are using.
Generally, the field types in a database are mainly 'text', 'number', and 'date/time'.
To use the WHERE clause for searching these fields, let¡¯s consider a few examples.
First, let¡¯s assume we created the database as follows:
The mdb file name is Test.mdb, and the table name below is Table2.
<Figure 1> The structure of Table2 in the mdb file
<Figure 2> The contents of records entered in Table2
To view the contents shown above using a monitoring program, you can use SQL statements with SQL connection settings. You can use the WHERE clause of SQL in the monitoring program to find and view the desired content.
The function to use the WHERE clause in the monitoring program is as follows:
@SQLSelect(DWORD id, string Table, string BindList, string whereExpr, string orderExpr);
@DatabaseSetFilter(string class_name, string where, string orderby); //This function is used when using database objects.
@SQLSelect() and @DatabaseSetFilter() differ in their connection methods to the DB. The former uses an ODBC connection, while the latter uses DAO. Thus, when using the latter, ODBC connection settings are not needed; you just need to enter the source file and table name.
Returning to the main point, users may have various conditions for searching. (For example, searching by date/time in the 'YMD' field, viewing the desired TYPE in the 'STR' field (text), or viewing the records of the desired number in the 'NUM' field (number), etc.)
We will now explain how to write conditions for each case. (Note: This method is applicable only in MS Access, and the format may vary slightly for each DB.)
WHERE
1. When the Field is in date/time format (In this case, even if the field is specified as 'text' and written in date format (i.e., text format as ¡®2001-03-10 11:30:11¡¯), it works fine.)
1) To retrieve values between selected dates
If the user wants to retrieve data between 2001/1/2 and 2001/1/5, the WHERE clause should be written as follows:
ACCESS SQL format -> Between #start month/start day/start year# And #end month/end day/end year#
In the monitoring program, the WHERE clause is written as follows: "YMD Between #01/02/01# And #01/05/01#" or "YMD Between #01/02/2001# And #01/05/2001#". Apply this WHERE clause to the function as follows:
@sprintf(buf,"YMD Between #01/02/2001# And #01/05/2001#");
@SQLSelect(ConnectID, Table name, Bindlist name, buf, "NUM ASC");
@DatabaseSetFilter(Class name, buf, "NUM ASC");
By writing this way, you can view the data for the selected dates sorted in ascending order ("NUM ASC" applied).
An example of using a database object is shown below.
<Figure 3> Using a database object to search the DB
Configure the screen as shown in the figure above.
(1) Insert the database object (ClassName -> Database1). Then, connect the DB file to the object properties. ¡°Editor|Database Reference¡±
(2) Configure memory tags for the tags (SYEAR, SMON, SDAY for start year/month/day, EYEAR, EMON, EDAY for end dates) to set the dates to be fetched.
(3) Configure the combo boxes for date settings. Configure ComboSyear, ComboSmon, ComboSday, ComboEyear, ComboEmon, ComboEday.
(4) Configure the script for the program button to search.
@sprintf(buf, "YMD Between #%02d/%02d/%04d# And #%02d/%02d/%04d#", $SMON,$SDAY,$SYEAR,$EMON,$EDAY+1,$EYEAR);
@DatabaseSetFilter("Database1", buf, "NUM ASC");
Description: %02d means it accepts the variable value from the back and makes it a 2-digit number. For example, if $SMON=1, $SDAY=2, $SYEAR=2001, $EMON=1, $EDAY=9, $SYEAR=2001, then the value stored in buf will be ¡°YMD Between #01/02/2001# And #01/09/2001#¡±.
The reason for adding +1 to $EDAY for the last date is to view up to the selected date. Without it, it fetches data only up to the day before the end date.
2) To retrieve all data for a specified date (Note: 1) is used to fetch values between days, while 2) is used to fetch data for a specified date (e.g., if the user wants to fetch data for 11 AM on January 2, 2001).)
To retrieve specified data, use the WHERE clause by considering the field as text. (Even if the field is in date/time format in the DB, if the WHERE clause is written in text format, the DB automatically adjusts it.) Note: This case requires the content to be written exactly as registered in the DB.
If the user wants to retrieve data for 11 AM on January 3, 2001, the WHERE clause should be written as follows:
@sprintf(buf,"YMD Like '01-01-01 11%%'");
@DatabaseSetFilter("database1",buf,"");
2. When the Field is in text format
1) For general strings
When the field is of 'text' type and you want to search a general string, for example, 'ABCDE', the WHERE clause should be written as follows:
@sprintf(buf, "STR='ABCDE'");
@SQLSelect(connect id, Table name, Bindlist name, buf, "");
@DatabaseSetFilter("class name", buf, "");
2) For strings with wildcards
When the field is of 'text' type and you want to search for a string containing 'AB', the WHERE clause should be written as follows:
@sprintf(buf, "STR Like 'AB%%'");
@SQLSelect(connect id, Table name, Bindlist name, buf, "");
@DatabaseSetFilter("class name", buf, "");
In this case, if the value in the STR field is 'ABCD', the value is retrieved, but if it is 'BCDA', it is not retrieved.
3. When the Field is in number format
1) To retrieve data based on a specific number
If the user wants to retrieve records with the value 123 in the NUM field, the WHERE clause should be written as follows:
@sprintf(buf, "NUM=123");
@SQLSelect(connect id, Table name, Bindlist name, buf, "");
@DatabaseSetFilter("class name", buf, "");
2) To retrieve data based on a range of numbers
If the user wants to retrieve records with values between 100 and 200 in the NUM field, the WHERE clause should be written as follows:
@sprintf(buf, "NUM Between 100 And 200");
@SQLSelect(connect id, Table name, Bindlist name, buf, "");
@DatabaseSetFilter("class name", buf, "");
By writing the WHERE clause as shown above, users can retrieve records based on specified conditions.
For further details, refer to the SQL syntax documentation for the database being used, as there might be variations in syntax and usage.